$webwork.htmlEncode($page.space.name) : Loading TIGER high-level-polygon name

This creates two tables - one that relates the TIGER module to state and state abreviation (ie. California/CA), and the other has the name of each county.

State Name and State Abbreviation ("state_translation")

name        | abrev | module
------------+-------+--------
 Alabama    | AL    | 01
 Alaska     | AK    | 02
 Arizona    | AZ    | 04
 Arkansas   | AR    | 05
 California | CA    | 06
...

County Name table ("county_names")

This relates a state/county code (module) to a county name. For example, TGR06017's county name is "El Dorado".

module  |                             name
----------+--------------------------------------------------------------
 TGR06015 | Del Norte
 TGR06017 | El Dorado
 TGR06019 | Fresno
...

CREATE TABLE county_names AS
   select module, name,state,county, datayr,fips,fipscc   FROM entitynames WHERE 
         fipscc = 'H1'  and fips isnull and datayr isnull and not(name like 'Balance of %');
 
-- should give the same number
select count(*) from county_names;
select count (distinct module) from county_names;
-- should be 0 ("Balance of County/Parish" isnt a real name)
select count(*) from county_names where  (name like 'Balance of %');
--should be 0 (module and state agree)
select * from county_names   
  WHERE substring(module  from 4 for 2)::int != state;
--should be 0 (module and county agree)
select * from county_names  
WHERE substring(module  from 6 for 3)::int != county;
--should be 0 (no state/county)
select * from county_names where county isnull or state isnull;


--add in the ones with H1 and a datayr  (should be 13)
INSERT into county_names 
select module, name,state,county, datayr,fips,fipscc from entitynames where fipscc = 'H1' and module in 
( -- this means all the ones we dont already have
	select county from counts_txt
	except
	select module  from county_names
) 
and substring(module  from 6 for 3)::int = county 
and substring(module  from 4 for 2)::int = state 
and datayr = '2000';

--This leaves us with about 100 "missing county names"  these are because some states
--code their counties as H4 or H6.  Lets start with H4.

INSERT INTO county_names
 select module, name,state,county, datayr,fips,fipscc   FROM entitynames WHERE 
         fipscc = 'H4'  and fips isnull and datayr isnull and not(name like 'Balance of %')
         and module in  -- this means all the ones we dont already have
        (
                select county from counts_txt
	 	except
	        select module  from county_names
	);

-- run the aboe QA/QC checks
--- now H4 with date
INSERT INTO county_names
 select module, name,state,county, datayr,fips,fipscc   FROM entitynames WHERE 
         fipscc = 'H4'  and fips isnull and datayr = '2000' and not(name like 'Balance of %')
         and module in  -- this means all the ones we dont already have
        (
                select county from counts_txt
	 	except
	        select module  from county_names
	);
	

-- now H6
INSERT INTO county_names
 select module, name,state,county, datayr,fips,fipscc   FROM entitynames WHERE 
         fipscc = 'H6'  and fips isnull and datayr isnull and not(name like 'Balance of %')
         and module in  -- this means all the ones we dont already have
        (
                select county from counts_txt
	 	except
	        select module  from county_names
	);
--run the above qa/qc checks

--now H6 with date
INSERT INTO county_names
 select module, name,state,county, datayr,fips,fipscc   FROM entitynames WHERE 
         fipscc = 'H6'  and fips isnull and datayr = '2000'  and not(name like 'Balance of %')
         and module in  -- this means all the ones we dont already have
        (
                select county from counts_txt
	 	except
	        select module  from county_names
	);
---2005
INSERT INTO county_names
 select module, name,state,county, datayr,fips,fipscc   FROM entitynames WHERE 
         fipscc = 'H6'  and fips isnull and datayr = '2005'  and not(name like 'Balance of %')
         and module in  -- this means all the ones we dont already have
        (
                select county from counts_txt
	 	except
	        select module  from county_names
	);

--run QA/QC checks

--now C7
INSERT INTO county_names
 select module, name,state,county, datayr,fips,fipscc   FROM entitynames WHERE 
         fipscc = 'C7'  and fips isnull and datayr isnull and not(name like 'Balance of %')
         and module in  -- this means all the ones we dont already have
        (
                select county from counts_txt
	 	except
	        select module  from county_names
	);
	
	
-- should give the same number (3232)
select count(*) from county_names;
select count (distinct module) from county_names;
-- should be 0 ("Balance of County/Parish" isnt a real name)
select count(*) from county_names where  (name like 'Balance of %');
--should be 0 (module and state agree)
select * from county_names   
  WHERE substring(module  from 4 for 2)::int != state;
--should be 0 (module and county agree)
select * from county_names  
WHERE substring(module  from 6 for 3)::int != county;
--should be 0 (no state/county)
select * from county_names where county isnull or state isnull;